//---------------------------------------------------------------------
// File: EntLibDBQueryStep.cs
// 
// Summary: 
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Threading;
using System.Xml;
using BizUnit.Extensions.EntLib.Utilities;
using Microsoft.Services.BizTalkApplicationFramework.BizUnit;

namespace BizUnit.Extensions
{
    /// <summary>
	/// The EntLibDBQueryStep is used to query a SQL query against a database and validate the values for the row returned.
	/// </summary>
	/// 
	/// <remarks>
	/// The following shows an example of the Xml representation of this test step.
	/// 
	/// <code escaped="true">
	///	<TestStep assemblyPath="" typeName="Microsoft.Services.BizTalkApplicationFramework.BizUnit.EntLibDBQueryStep">
	///		<DelayBeforeCheck>1</DelayBeforeCheck>
    ///		<ConfigFileLocation>..\\..\\db.config</ConnectionString>
    ///		<DatabaseConfigName>Northwind</DatabaseConfigName>
	///		<!-- 
	///		The SQL Query to execute is built by formatting the RawSQLQuery substituting in the 
	///		SQLQueryParam's
	///		-->
	///		<SQLQuery>
	///			<RawSQLQuery>select * from dbo.bam_Event_Completed where EventId = {0}</RawSQLQuery>
	///			<SQLQueryParams>
	///				<SQLQueryParam takeFromCtx="EventId"></SQLQueryParam>
	///			</SQLQueryParams>
	///		</SQLQuery>
	///			
	///		<Rows>
	///			<Columns>
	///			<!-- 
	///			Note: The column names are dependant on the DB schema being checked against.
	///			Adding the attribute isUnique="true" to one of the columns allows it to be 
	///			uniquely selcted in the scenario where multiple rows are returned.
	///			-->
	///					
	///				<EventType>Switch</EventType>
	///				<EventStatus>Completed</EventStatus>
	///				<ProcessorId>JVQFFCCZ0</ProcessorId>
	///				<SchemeId>GF81300000</SchemeId>
	///				<EventFailed>null</EventFailed>
	///				<EventHeld>null</EventHeld>
	///				<EventHoldNotifRec>null</EventHoldNotifRec>
	///			</Columns>
	///		</Rows>	
	///	</TestStep>
	///	</code>
	///	
	///	The ContextManipulator builds a new context item by appeanding the values of multiple context items
	///	<list type="table">
	///		<listheader>
	///			<term>Tag</term>
	///			<description>Description</description>
	///		</listheader>
	///		<item>
	///			<term>DelayBeforeCheck</term>
	///			<description>The number of seconds to wait before executing the step</description>
	///		</item>
    ///		<item>
    ///			<term>ConfigFileLocation</term>
    ///			<description>Location of the Database configuration file</description>
    ///		</item>
    ///     <item>
    ///			<term>DatabaseConfigName</term>
    ///			<description>Name of the Database to be used in the configuration file</description>
    ///		</item>
	///		<item>
	///			<term>SQLQuery/RawSQLQuery</term>
	///			<description>The raw SQL string that will be formatted by substituting in the SQLQueryParam</description>
	///		</item>
	///		<item>
	///			<term>SQLQuery/SQLQueryParams/SQLQueryParam</term>
	///			<description>The parameters to substitute into RawSQLQuery <para>(repeating)</para></description>
	///		</item>
	///		<item>
	///			<term>Rows/Columns</term>
	///			<description>One or more columns which represent the expected query result</description>
	///		</item>
	///		<item>
	///			<term>Rows/Columns/User defined element</term>
	///			<description>The fields that are validated in the response</description>
	///		</item>
	///	</list>
	///	</remarks>
	
	public class EntLibDBQueryStep : ITestStep
	{
		/// <summary>
		/// ITestStep.Execute() implementation
		/// </summary>
		/// <param name='testConfig'>The Xml fragment containing the configuration for this test step</param>
		/// <param name='context'>The context for the test, this holds state that is passed beteen tests</param>
		public void Execute(XmlNode testConfig, Context context)
		{
			int delayBeforeCheck = context.ReadConfigAsInt32( testConfig, "DelayBeforeCheck" );
            string configFileLocation = context.ReadConfigAsString(testConfig, "ConfigFileLocation");
            string databaseConfigName = context.ReadConfigAsString(testConfig, "DatabaseConfigName");
		    
			XmlNode queryConfig = testConfig.SelectSingleNode( "SQLQuery" );
			string sqlQuery = EntLibDatabaseHelper.BuildSQLQuery( queryConfig, context );

            context.LogInfo("Using database connection string from : {0}", configFileLocation);
			context.LogInfo("Executing database query: {0}", sqlQuery );

			XmlNode rowCollection = testConfig.SelectSingleNode( "Rows" );

			// Sleep for delay seconds...
			Thread.Sleep(delayBeforeCheck*1000);
			
			XmlNodeList bamValidationRows = rowCollection.SelectNodes("*");

            DataSet ds = FillDataSet(configFileLocation, databaseConfigName, sqlQuery);

			DataRow row = ds.Tables[0].Rows[0];			

			foreach (XmlNode bamValidationRow in bamValidationRows)				
			{
				XmlNodeList bamValidationCols = bamValidationRow.SelectNodes("*");
				DataRow bamDBRow = null;

				// Get the element which has the unique flag on...
				XmlNode indexColumn = bamValidationRow.SelectSingleNode( "*[@isUnique='true']" );
				if ( null != indexColumn )
				{
					DataRow[] bamDBRowArray = ds.Tables[0].Select( string.Format( "{0} = '{1}'", indexColumn.LocalName, indexColumn.InnerText ) );
					bamDBRow = bamDBRowArray[0];
				}
				else
				{
					bamDBRow = ds.Tables[0].Rows[0];
				}
				
				foreach (XmlNode bamValidationCol in bamValidationCols)				
				{
					object dbData = bamDBRow[ bamValidationCol.LocalName ];
					string dbDataStringValue = "";
					if ( 0 == ValidateData( dbData, bamValidationCol.InnerText, ref dbDataStringValue ) )
					{
						context.LogInfo("Validation succeeded for field: {0} equals: {1}", bamValidationCol.LocalName, dbDataStringValue);
					}
					else
					{
						throw new Exception(String.Format("Validation failed for field: {0}. Expected value: {1}, actual value: {2}", bamValidationRow.LocalName, bamValidationCol.InnerText, dbDataStringValue));
					}
				}
			}
		}

		private int ValidateData( object dbData, string targetValue, ref string dbDataStringValue)
		{
			dbDataStringValue = Convert.ToString(dbData);

			switch( dbData.GetType().ToString() )
			{
				case( "System.DateTime" ):
					DateTime dbDt = (DateTime)dbData;
					DateTime targetDT = Convert.ToDateTime(targetValue);
					return targetDT.CompareTo( dbDt );

				case( "System.DBNull" ):
					dbDataStringValue = "null";
					return targetValue.CompareTo( "null" );

				case( "System.String" ):
					dbDataStringValue = (string)dbData;
					return targetValue.CompareTo( (string)dbData );

				case( "System.Int16" ):
					Int16 dbInt16 = (Int16)dbData;
					Int16 targetInt16 = Convert.ToInt16(targetValue);
					return targetInt16.CompareTo( dbInt16 );

				case( "System.Int32" ):
					Int32 dbInt32 = (Int32)dbData;
					Int32 targetInt32 = Convert.ToInt32(targetValue);
					return targetInt32.CompareTo( dbInt32 );

				case( "System.Int64" ):
					Int64 dbInt64 = (Int64)dbData;
					Int64 targetInt64 = Convert.ToInt64(targetValue);
					return targetInt64.CompareTo( dbInt64 );

				case( "System.Double" ):
					Double dbDouble = (Double)dbData;
					Double targetDouble = Convert.ToDouble(targetValue);
					return targetDouble.CompareTo( dbDouble );

				case( "System.Decimal" ):
					Decimal dbDecimal = (Decimal)dbData;
					Decimal targetDecimal = Convert.ToDecimal(targetValue);
					return targetDecimal.CompareTo( dbDecimal );

				case( "System.Boolean" ):
					Boolean dbBoolean = (Boolean)dbData;
					Boolean targetBoolean = Convert.ToBoolean(targetValue);
					return targetBoolean.CompareTo( dbBoolean );

				case( "System.Byte" ):
					Byte dbByte = (Byte)dbData;
					Byte targetByte = Convert.ToByte(targetValue);
					return targetByte.CompareTo( dbByte );

				case( "System.Char" ):
					Char dbChar = (Char)dbData;
					Char targetChar = Convert.ToChar(targetValue);
					return targetChar.CompareTo( dbChar );

				case( "System.SByte" ):
					SByte dbSByte = (SByte)dbData;
					SByte targetSByte = Convert.ToSByte(targetValue);
					return targetSByte.CompareTo( dbSByte );

				default:
					throw new ApplicationException(string.Format("Unsupported type: {0}", dbData.GetType()) );
			};
		}

		private DataSet FillDataSet(string configFileLocation, string databaseConfigName, string sqlCommand)
		{
            return EntLibDatabaseHelper.ExecuteSqlCommand(configFileLocation, databaseConfigName, sqlCommand);
	    }
	}
}
